﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;using System.Configuration;
using System.Windows.Forms;

namespace ChairtyBank.DAL
{
    public class NotesEntity
    {
        public Int32 C_Id { get; set; }
        public String C_Note { get; set; }
 
    }

    class CNotes
    {
        //Set absolute DB path rather than relative to solve openFileDialog reset path problem
        private static string conString = ConfigurationManager.ConnectionStrings["ChairtyBank"].ToString();
        private static string cmdText = "";



        public static NotesEntity GetNote(int C_Id)
        {
            NotesEntity c = null;

            cmdText = "select * from CNotes where C_Id=@Id";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Id",C_Id);
            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    c = fillEntityFromReader(reader);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("NotesEntity.GetUser()==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return c;
        }


        public static List<NotesEntity> GetAllNotes()
        {
            NotesEntity c = null;
            List<NotesEntity> Notes = new List<NotesEntity>();

            cmdText = "select * from CNotes";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);

            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    c = fillEntityFromReader(reader);
                    Notes.Add(c);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("NotesEntity.GetAllUsers()==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return Notes;

        }

        public static bool Add(NotesEntity c)
        {
            bool flag = true;
            cmdText = "insert into CNotes (C_Id,C_Note)values(@C_Id,@C_Note)";

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@C_Id", c.C_Id);
            cmd.Parameters.AddWithValue("@C_Note", c.C_Note);

            try
            {
                con.Open();
                Int32 numOfRow = cmd.ExecuteNonQuery();
                if (numOfRow == 0)
                    flag = false;

            }
            catch (Exception ex)
            {
                flag = false;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;

        }

        public static bool Update(NotesEntity c)
        {
            Boolean flag = false;
            cmdText = "UPDATE CNotes SET C_Note=@C_Note WHERE C_Id=@Id";

            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@C_Note", c.C_Note);
            cmd.Parameters.AddWithValue("@Id", c.C_Id);
            try
            {
                con.Open();
                Int32 numOfRow = cmd.ExecuteNonQuery();
                flag = numOfRow > 0? true:false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("NotesEntity.Update==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;
        }


        /*
           Delete usingC_Id or Serial 
        */

        public static bool Delete(Int32 c)
        {
            Boolean flag = false;
            cmdText = "Delete from CNotes WHERE C_Id=@Id";


            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Id", c);
            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
                flag =  numOfRow > 0? true:false;


            }
            catch (Exception ex)
            {
                MessageBox.Show("NotesEntity.Delete==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();

            }
            return flag;
        }




        private static NotesEntity fillEntityFromReader(SqlDataReader reader)
        {
            NotesEntity c = new NotesEntity();
            c.C_Id = Int32.Parse(reader["C_Id"].ToString());

            if (reader["C_Note"] != null)
                c.C_Note = reader["C_Note"].ToString();
              
            return c;
        }


    }
}